﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.Threading;

namespace NikeSale.Dal
{
    public static class DBHelper
    {
        /// <summary>
        /// 数据存储器
        /// </summary>
        public static DataSet DtSet { get; private set; } = new DataSet("DtSet");
        /// <summary>
        /// 数据适配器
        /// </summary>
        public static SqlDataAdapter Adapter { get; private set; } = new SqlDataAdapter();
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private static string ConnString = Settings1.Default.连接字符串;
        /// <summary>
        /// 数据库连接对象
        /// </summary>
        public static SqlConnection conn { get; private set; } = new SqlConnection(ConnString);
        /// <summary>
        /// 数据库命令对象
        /// </summary>
        private static SqlCommand cmd = new SqlCommand() { Connection = conn };

        /// <summary>
        /// 纠正连接对象的状态
        /// </summary>
        private static void InitConnection()
        {
            if (conn == null)
            {
                conn = new SqlConnection(ConnString);
            }
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            if (conn.State == ConnectionState.Broken)
            {
                conn.Close();
                conn.Open();
            }
        }

        /// <summary>
        /// 检测Sql服务是否打开
        /// </summary>
        /// <returns></returns>
        public static bool SqlServiceIsOpen()
        {
            if (conn.State == ConnectionState.Closed)
            {
                Thread thread = new Thread(() =>
                {
                    conn.Open();
                })
                { IsBackground = true };
                thread.Start();
                thread.Join(3000);
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    return true;
                }
                else
                {
                    thread.Abort();
                    return false;
                }
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 返回QueryResults的DataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sql)
        {
            InitConnection();
            cmd.CommandText = sql;
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// 返回执行QueryResults后的数据表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>包含QueryResults的数据表</returns>
        public static DataTable GetTable(string sql,string tableName)
        {
            InitConnection();
            cmd.CommandText = sql;
            Adapter.SelectCommand = cmd;
            Adapter.Fill(DtSet,tableName);
            return DtSet.Tables[tableName];
        }

        /// <summary>
        /// 从DataSet中删除指定表
        /// </summary>
        /// <param name="tableName"></param>
        public static void RemoveTable(string tableName)
        {
            DataTable tb = DtSet.Tables[tableName];
            if (DtSet.Tables.CanRemove(tb))
            {
                DtSet.Tables.Remove(tb);
            }
            else
            {
                throw new Exception("不能删除表["+tableName+"]");
            }
            
        }

        /// <summary>
        /// 检测DataSet中是否包含指定的表
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public static bool HasTable(string tablename)
        {
            return DtSet.Tables.Contains(tablename);
        }

        /// <summary>
        /// 逆向更新数据库
        /// </summary>
        /// <param name="tableName">关联的表名</param>
        public static int DataSetUpdate(string tableName,string sqlText)
        {
            if (!DtSet.Tables.Contains(tableName))
            {
                throw new Exception("不存在表" + tableName);
            }
            cmd.CommandText = sqlText;
            Adapter.SelectCommand = cmd;
            SqlCommandBuilder builder = new SqlCommandBuilder(Adapter);
            return Adapter.Update(DtSet,tableName);
        }

        /// <summary>
        /// 增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>执行成功返回true</returns>
        public static bool ExecuteNonQuery(string sql)
        {
            InitConnection();
            cmd.CommandText = sql;
            return cmd.ExecuteNonQuery() > 0;
        }

        /// <summary>
        /// 返回第一行第一列的值
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql)
        {
            InitConnection();
            cmd.CommandText = sql;
            return cmd.ExecuteScalar();
        }
    }
}
